﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Agresso.Interface.Layer.SQL
{
    public class CommisionInvoiceSQL
    {
        public static string GetAllCommissionInvoice()
        {
            return @"
            Select distinct 
                    vOpInvoiceSrch.Type invoice_type,
                    vInvoiceSrch.Company company,
                    vVMVoyageSrch.VesselName vessel_name,
                    vVMVoyageSrch.Vessel vessel_code,
                    vVMVoyageSrch.VoyageNo voyage_no,
                    vProfitCentreOrganisationSrch.ProfitCentreName profit_centre,
                    vInvoiceSrch.DocInvDate invoice_date,----------
                    vOpInvoiceSrch.Narrative charge_description,
                    vInvoiceSrch.InvoiceNo invoice_number,
                    vInvoiceSrch.InvoiceTotal invoice_amount,
                    vInvoiceSrch.Curr invoice_currency,
                    --vOpchargeFixtureSrch.InvoiceGroup Invoiced,
                    vInvoiceSrch.DueDate invoice_due_date,
                    vOpInvoiceSrch.ChargeType charge_type,
                    vInvoiceSrch.RaisedToFinalBy raised_to_final_by,
                    --vInvoiceSrch.Raised Raised,
                    --vInvoiceSrch.RaisedStatus Raised_Status,
                    vInvoiceSrch.Paid paid,
                    vInvoiceSrch.PaidDate paid_date,
                    vOpInvoiceSrch.InvChargeAddrComm charge_addr_comm,
                    vOpInvoiceSrch.InvChargeAmount charge_amount, --For Charge Invoice
                    vOpInvoiceSrch.InvChargeCoComm charge_collect_comm,
                    vOpInvoiceSrch.InvChargeNonCoComm charge_non_collect_comm,
                    --Cast(vInvoiceSrch.DocInvDate as float)+2 Calc_DocInvDate,
                    --Cast(vInvoiceSrch.DueDate as float)+2 Calc_DueDate
                    vVMVoyageSrch.Type voyage_type
                    , VOPCHARGEFIXTURESRCH.CollectComm1 collect_comm
                    , VOPCHARGEFIXTURESRCH.CollectComm2 collect_comm2
                    , VOPCHARGEFIXTURESRCH.CollectComm3 collect_comm3
                    , VOPCHARGEFIXTURESRCH.CollectComm4 collect_comm4
                     FROM 
                     VVMVOYAGESRCH 
                     JOIN VINVOICESRCH ON VVMVOYAGESRCH.ID = VINVOICESRCH.VOYAGE 
                     JOIN VVMOPFIXSRCH ON VVMVOYAGESRCH.ID = VVMOPFIXSRCH.VMVOYAGENO 
                     JOIN VPROFITCENTREORGANISATIONSRCH ON VVMVOYAGESRCH.ID = VPROFITCENTREORGANISATIONSRCH.ID 
                     JOIN VDEPTORGANISATIONSRCH ON VVMVOYAGESRCH.ID=VDEPTORGANISATIONSRCH.ID 
                     LEFT OUTER JOIN VOPINVOICESRCH ON VVMVOYAGESRCH.ID=VOPINVOICESRCH.VOY AND 
			                     VINVOICESRCH.FIX=VOPINVOICESRCH.FIX AND 
			                     VINVOICESRCH.OPNO=VOPINVOICESRCH.OPNO AND 
			                     VINVOICESRCH.VYNO=VOPINVOICESRCH.VYNO AND 
			                     VINVOICESRCH.GRNO=VOPINVOICESRCH.GRNO AND 
			                     VINVOICESRCH.NO=VOPINVOICESRCH.NO 
                     LEFT OUTER JOIN VOPCHARGEFIXTURESRCH ON VVMOPFIXSRCH.OPSFIXTURE = VOPCHARGEFIXTURESRCH.FIXTURE 
			                     AND  VVMOPFIXSRCH.OPSOPTIONNO = VOPCHARGEFIXTURESRCH.OPSOPTIONNO 
			                     AND  VVMOPFIXSRCH.OPSVOYAGENO = VOPCHARGEFIXTURESRCH.OPSVOYAGENO 
                                AND  VOPCHARGEFIXTURESRCH.Reversal = 0 
								 and VOPCHARGEFIXTURESRCH.Reversed = 0 
							     and VOPCHARGEFIXTURESRCH.Narrative = vOpInvoiceSrch.Narrative
                    -- CROSS JOIN VSSLCLIENTCONFIG 
                     WHERE 
                     vVMVoyageSrch.Category NOT LIKE 'UnFixed' AND 
                     vInvoiceSrch.InvoiceNo <> 'proforma' and
                     vProfitCentreOrganisationSrch.ProfitCentreName <>  'PACCSHIP (UK) LTD' AND
                     vInvoiceSrch.Paid = 0 AND
                     vInvoiceSrch.DocInvDate > '2013-01-01 00:00'
            ";
        }
        public static string GetCommisionInvoiceByTCReletTypeAndInvoiceTypeCommissionXX()
        {
            return @"
                   Select distinct 
                    VVMVOYAGESRCH.ID,
                    vOpInvoiceSrch.Type invoice_type,
                    vInvoiceSrch.Company company,
                    vVMVoyageSrch.VesselName vessel_name,
                    vVMVoyageSrch.Vessel vessel_code,
                    vVMVoyageSrch.VoyageNo voyage_no,
                    vProfitCentreOrganisationSrch.ProfitCentreName profit_centre,
                    vInvoiceSrch.DocInvDate invoice_date,----------
                    vOpInvoiceSrch.Narrative charge_description,
                    vInvoiceSrch.InvoiceNo invoice_number,
                    vInvoiceSrch.InvoiceTotal invoice_amount,
                    vInvoiceSrch.Curr invoice_currency,
                    --vOpchargeFixtureSrch.InvoiceGroup Invoiced,
                    vInvoiceSrch.DueDate invoice_due_date,
                    vOpInvoiceSrch.ChargeType charge_type,
                    vInvoiceSrch.RaisedToFinalBy raised_to_final_by,
                    --vInvoiceSrch.Raised Raised,
                    --vInvoiceSrch.RaisedStatus Raised_Status,
                    vInvoiceSrch.Paid paid,
                    vInvoiceSrch.PaidDate paid_date,
                    vOpInvoiceSrch.InvChargeAddrComm charge_addr_comm,
                    vOpInvoiceSrch.InvChargeAmount charge_amount, --For Charge Invoice
                    vOpInvoiceSrch.InvChargeCoComm charge_collect_comm,
                    vOpInvoiceSrch.InvChargeNonCoComm charge_non_collect_comm,
                    --Cast(vInvoiceSrch.DocInvDate as float)+2 Calc_DocInvDate,
                    --Cast(vInvoiceSrch.DueDate as float)+2 Calc_DueDate
                    vVMVoyageSrch.Type voyage_type
                    , VOPCHARGEFIXTURESRCH.CollectComm1 collect_comm
                    ,VOPCHARGEFIXTURESRCH.CollectComm2 collect_comm2
                    ,VOPCHARGEFIXTURESRCH.CollectComm3 collect_comm3
                    ,VOPCHARGEFIXTURESRCH.CollectComm4 collect_comm4
                   ,VOPINVOICELINENARRATIVE.DisplayOrder display_order
                    ,VOPINVOICELINENARRATIVE.ChargeID charge_id
                    ,VOPINVOICELINENARRATIVE.NarrativeLine narrative_line
                     FROM 
                     VVMVOYAGESRCH 
                     JOIN VINVOICESRCH ON VVMVOYAGESRCH.ID = VINVOICESRCH.VOYAGE 
                     JOIN VVMOPFIXSRCH ON VVMVOYAGESRCH.ID = VVMOPFIXSRCH.VMVOYAGENO 
                     JOIN VPROFITCENTREORGANISATIONSRCH ON VVMVOYAGESRCH.ID = VPROFITCENTREORGANISATIONSRCH.ID                      
                     LEFT OUTER JOIN VOPINVOICESRCH ON VVMVOYAGESRCH.ID=VOPINVOICESRCH.VOY AND 
			                     VINVOICESRCH.FIX=VOPINVOICESRCH.FIX AND 
			                     VINVOICESRCH.OPNO=VOPINVOICESRCH.OPNO AND 
			                     VINVOICESRCH.VYNO=VOPINVOICESRCH.VYNO AND 
			                     VINVOICESRCH.GRNO=VOPINVOICESRCH.GRNO AND 
			                     VINVOICESRCH.NO=VOPINVOICESRCH.NO 
                     LEFT OUTER JOIN VOPCHARGEFIXTURESRCH ON VVMOPFIXSRCH.OPSFIXTURE = VOPCHARGEFIXTURESRCH.FIXTURE 
			                     AND  VVMOPFIXSRCH.OPSOPTIONNO = VOPCHARGEFIXTURESRCH.OPSOPTIONNO 
			                     AND  VVMOPFIXSRCH.OPSVOYAGENO = VOPCHARGEFIXTURESRCH.OPSVOYAGENO 
			                     AND  VOPCHARGEFIXTURESRCH.Reversal = 0 
								 and VOPCHARGEFIXTURESRCH.Reversed = 0 
							     and VOPCHARGEFIXTURESRCH.Narrative = vOpInvoiceSrch.Narrative
                    LEFT OUTER JOIN VOPINVOICELINENARRATIVE ON
                                VOPINVOICELINENARRATIVE.ChargeID = vOpInvoiceSrch.ChargeId  and 
                                VOPINVOICELINENARRATIVE.InvoiceNo = VINVOICESRCH.No and
                                VOPINVOICELINENARRATIVE.Charge is not null and              
                                VOPINVOICELINENARRATIVE.NarrativeLine not in(70,80,90)
                    -- CROSS JOIN VSSLCLIENTCONFIG 
                     WHERE 
                     vVMVoyageSrch.Category NOT LIKE 'UnFixed' AND 
                     vInvoiceSrch.InvoiceNo <> 'proforma' and
                     vProfitCentreOrganisationSrch.ProfitCentreName <>  'PACCSHIP (UK) LTD' AND
                     --vInvoiceSrch.Paid = 0 AND
                     vInvoiceSrch.DocInvDate > '2013-07-23 00:00' and 
                     vOpInvoiceSrch.Type in ('Commission Credit', 'Commission Debit') AND
                     vVMVoyageSrch.Type = 'TC Relet' AND
                    vInvoiceSrch.InvoiceNo not like 'PPL%'
                    order by 
                    vInvoiceSrch.InvoiceNo,
                    VOPINVOICELINENARRATIVE.DisplayOrder,
                    VOPINVOICELINENARRATIVE.ChargeID,
                    VOPINVOICELINENARRATIVE.NarrativeLine
                    ";
        }
        public static string GetMaxVoyageNoByVesselName()
        {
            return @"select max(vVMVoyageSrch.VoyageNo) from vVMVoyageSrch where VesselName = @vesselname";
        }
        public static string GetCommisionInvoiceByTCReletTypeAndInvoiceTypeCommission()
        {
            return @"
                 select 
	                    VVMVOYAGESRCH.ID,
	                    vInvoiceSrch.Type invoice_type,
	                    vInvoiceSrch.Company company,
	                    vVMVoyageSrch.VesselName vessel_name,
	                    vVMVoyageSrch.Vessel vessel_code,
	                    vVMVoyageSrch.VoyageNo voyage_no,
	                    vProfitCentreOrganisationSrch.ProfitCentreName profit_centre,
	                    vInvoiceSrch.DocInvDate invoice_date,----------
	                    VOPINVOICELINENARRATIVE.Narrative charge_description,
	                    vInvoiceSrch.InvoiceNo invoice_number,
	                    vInvoiceSrch.InvoiceTotal invoice_amount,
	                    vInvoiceSrch.Curr invoice_currency,
	                    vInvoiceSrch.DueDate invoice_due_date,
	                    VOPINVOICELINENARRATIVE.Type charge_type,
	                    vInvoiceSrch.RaisedToFinalBy raised_to_final_by,
	                    vInvoiceSrch.Paid paid,
	                    vInvoiceSrch.PaidDate paid_date,
	                    0 charge_addr_comm,
	                    VOPINVOICELINENARRATIVE.Charge charge_amount, 
	                    0 charge_collect_comm,
                        0 charge_non_collect_comm,
	                    vVMVoyageSrch.Type voyage_type,
	                    VOPCHARGEFIXTURESRCH.CollectComm1 collect_comm,
	                    VOPCHARGEFIXTURESRCH.CollectComm2 collect_comm2,
	                    VOPCHARGEFIXTURESRCH.CollectComm3 collect_comm3,
	                    VOPCHARGEFIXTURESRCH.CollectComm4 collect_comm4,
	                    VOPINVOICELINENARRATIVE.DisplayOrder display_order,
	                    VOPINVOICELINENARRATIVE.ChargeID charge_id,
	                    VOPINVOICELINENARRATIVE.NarrativeLine narrative_line,
	                    VOPCHARGEFIXTURESRCH.Narrative narrative		   
                                        from 
                     vInvoiceSrch 
                      join VVMVOYAGESRCH on VVMVOYAGESRCH.ID = VINVOICESRCH.VOYAGE 
                     join VOPINVOICELINENARRATIVE on
                     VOPINVOICELINENARRATIVE.InvoiceNo = VINVOICESRCH.No
                     and  VOPINVOICELINENARRATIVE.Fixture = VINVOICESRCH.Fix and
                                                    VOPINVOICELINENARRATIVE.InvoiceNo = vInvoiceSrch.No and
                                                    VOPINVOICELINENARRATIVE.Charge is not null  and
                                                    VOPINVOICELINENARRATIVE.VoyageNo =  vInvoiceSrch.VyNo and
                                                    VOPINVOICELINENARRATIVE.GroupNo = vInvoiceSrch.GrNo and
                                                    VOPINVOICELINENARRATIVE.OptionNo = vInvoiceSrch.OpNo
                     JOIN VPROFITCENTREORGANISATIONSRCH ON VVMVOYAGESRCH.ID = VPROFITCENTREORGANISATIONSRCH.ID       
                     join VOPCHARGEFIXTURESRCH on VOPCHARGEFIXTURESRCH.ID = VOPINVOICELINENARRATIVE.ChargeID
                    where
                    --vInvoiceSrch.InvoiceNo = 'MBC PADU/202'  
                    VOPINVOICELINENARRATIVE.NarrativeLine in(80)--    Commission CREDIT-- ALL --take collect commision
                    and vInvoiceSrch.DocInvDate > '2013-07-23 00:00'
                    and VOPINVOICELINENARRATIVE.Charge is not null
                    and vInvoiceSrch.Type in ('Commission Credit', 'Commission Debit') 
                    AND vVMVoyageSrch.Type in ('Cargo Relet','Voyage','TC Relet' )
                    AND vInvoiceSrch.InvoiceNo not like 'PPL%'
                    AND  vVMVoyageSrch.Category NOT LIKE 'UnFixed'  
                    AND  vInvoiceSrch.InvoiceNo <> 'proforma'
                    AND  vProfitCentreOrganisationSrch.ProfitCentreName <>  'PACCSHIP (UK) LTD'
                    AND  vProfitCentreOrganisationSrch.ProfitCentreName <> 'PACNAV'
                    OR
                    (
                    VOPINVOICELINENARRATIVE.NarrativeLine in(80)--    Commission CREDIT-- ALL --take collect commision
                    and vInvoiceSrch.DocInvDate > '2014-01-01 00:00'
                    and VOPINVOICELINENARRATIVE.Charge is not null
                    and vInvoiceSrch.Type in ('Commission Credit', 'Commission Debit') 
                    AND vVMVoyageSrch.Type in ('Cargo Relet','Voyage','TC Relet' )
                    AND vInvoiceSrch.InvoiceNo not like 'PPL%'
                    AND  vVMVoyageSrch.Category NOT LIKE 'UnFixed'  
                    AND  vInvoiceSrch.InvoiceNo <> 'proforma'
                    AND  vProfitCentreOrganisationSrch.ProfitCentreName =  'PACCSHIP (UK) LTD'
                    )
                     order by 
                    vInvoiceSrch.InvoiceNo,
                    VOPINVOICELINENARRATIVE.DisplayOrder,
                    VOPINVOICELINENARRATIVE.ChargeID,
                    VOPINVOICELINENARRATIVE.NarrativeLine  
                    ";
        }
        public static string GetCommisionInvoiceByVCReletTypeAndInvoiceTypeCommission()
        {
            return @"
                    Select distinct 
                    VVMVOYAGESRCH.ID,
                    vOpInvoiceSrch.Type invoice_type,
                    vInvoiceSrch.Company company,
                    vVMVoyageSrch.VesselName vessel_name,
                    vVMVoyageSrch.Vessel vessel_code,
                    vVMVoyageSrch.VoyageNo voyage_no,
                    vProfitCentreOrganisationSrch.ProfitCentreName profit_centre,
                    vInvoiceSrch.DocInvDate invoice_date,----------
                    vOpInvoiceSrch.Narrative charge_description,
                    vInvoiceSrch.InvoiceNo invoice_number,
                    vInvoiceSrch.InvoiceTotal invoice_amount,
                    vInvoiceSrch.Curr invoice_currency,
                    --vOpchargeFixtureSrch.InvoiceGroup Invoiced,
                    vInvoiceSrch.DueDate invoice_due_date,
                    vOpInvoiceSrch.ChargeType charge_type,
                    vInvoiceSrch.RaisedToFinalBy raised_to_final_by,
                    --vInvoiceSrch.Raised Raised,
                    --vInvoiceSrch.RaisedStatus Raised_Status,
                    vInvoiceSrch.Paid paid,
                    vInvoiceSrch.PaidDate paid_date,
                    vOpInvoiceSrch.InvChargeAddrComm charge_addr_comm,
                    vOpInvoiceSrch.InvChargeAmount charge_amount, --For Charge Invoice
                    vOpInvoiceSrch.InvChargeCoComm charge_collect_comm,
                    vOpInvoiceSrch.InvChargeNonCoComm charge_non_collect_comm,
                    --Cast(vInvoiceSrch.DocInvDate as float)+2 Calc_DocInvDate,
                    --Cast(vInvoiceSrch.DueDate as float)+2 Calc_DueDate
                    vVMVoyageSrch.Type voyage_type
                    ,0 collect_comm
                    ,0 collect_comm2
                    ,0 collect_comm3
                    ,0 collect_comm4
                    ,VOPINVOICELINENARRATIVE.DisplayOrder display_order
                    ,VOPINVOICELINENARRATIVE.ChargeID charge_id
                    ,VOPINVOICELINENARRATIVE.NarrativeLine narrative_line
                     FROM 
                     VVMVOYAGESRCH 
                     JOIN VINVOICESRCH ON VVMVOYAGESRCH.ID = VINVOICESRCH.VOYAGE                      
                     JOIN VPROFITCENTREORGANISATIONSRCH ON VVMVOYAGESRCH.ID = VPROFITCENTREORGANISATIONSRCH.ID                      
                     LEFT OUTER JOIN VOPINVOICESRCH ON VVMVOYAGESRCH.ID=VOPINVOICESRCH.VOY AND 
			                     VINVOICESRCH.FIX=VOPINVOICESRCH.FIX AND 
			                     VINVOICESRCH.OPNO=VOPINVOICESRCH.OPNO AND 
			                     VINVOICESRCH.VYNO=VOPINVOICESRCH.VYNO AND 
			                     VINVOICESRCH.GRNO=VOPINVOICESRCH.GRNO AND 
			                     VINVOICESRCH.NO=VOPINVOICESRCH.NO           
                    LEFT OUTER JOIN VOPINVOICELINENARRATIVE ON
                                VOPINVOICELINENARRATIVE.ChargeID = vOpInvoiceSrch.ChargeId  and 
                                VOPINVOICELINENARRATIVE.InvoiceNo = VINVOICESRCH.No and
                                VOPINVOICELINENARRATIVE.Charge is not null and              
                                VOPINVOICELINENARRATIVE.NarrativeLine not in(70,80,90)          
                    -- CROSS JOIN VSSLCLIENTCONFIG 
                     WHERE 
                     vVMVoyageSrch.Category NOT LIKE 'UnFixed' AND 
                     vInvoiceSrch.InvoiceNo <> 'proforma' and
                     vProfitCentreOrganisationSrch.ProfitCentreName <>  'PACCSHIP (UK) LTD' AND
                    -- vInvoiceSrch.Paid = 0 AND 01072013
                     vInvoiceSrch.DocInvDate > '2013-07-23 00:00' AND
                     vOpInvoiceSrch.Type in ('Commission Credit', 'Commission Debit') AND
                     vVMVoyageSrch.Type in ('Cargo Relet','Voyage')  AND
                    vInvoiceSrch.InvoiceNo not like 'PPL%'
                    order by 
                    vInvoiceSrch.InvoiceNo,
                    VOPINVOICELINENARRATIVE.DisplayOrder,
                    VOPINVOICELINENARRATIVE.ChargeID,
                    VOPINVOICELINENARRATIVE.NarrativeLine
                    ";
        }
        public static string GetCommisionInvoiceByTCReletTypeAndInvoiceTypeCommissionByDate()
        {
            return @"
                    Select distinct 
                    vOpInvoiceSrch.Type invoice_type,
                    vInvoiceSrch.Company company,
                    vVMVoyageSrch.VesselName vessel_name,
                    vVMVoyageSrch.Vessel vessel_code,
                    vVMVoyageSrch.VoyageNo voyage_no,
                    vProfitCentreOrganisationSrch.ProfitCentreName profit_centre,
                    vInvoiceSrch.DocInvDate invoice_date,----------
                    vOpInvoiceSrch.Narrative charge_description,
                    vInvoiceSrch.InvoiceNo invoice_number,
                    vInvoiceSrch.InvoiceTotal invoice_amount,
                    vInvoiceSrch.Curr invoice_currency,
                    --vOpchargeFixtureSrch.InvoiceGroup Invoiced,
                    vInvoiceSrch.DueDate invoice_due_date,
                    vOpInvoiceSrch.ChargeType charge_type,
                    vInvoiceSrch.RaisedToFinalBy raised_to_final_by,
                    --vInvoiceSrch.Raised Raised,
                    --vInvoiceSrch.RaisedStatus Raised_Status,
                    --vInvoiceSrch.Paid Paid,
                    --vInvoiceSrch.PaidDate Paid_Date,
                    --vOpInvoiceSrch.InvChargeAddrComm Charge_Addr_Comm,
                    vOpInvoiceSrch.InvChargeAmount charge_amount --For Charge Invoice
                    --vOpInvoiceSrch.InvChargeCoComm Charge_Collect_Comm,
                    --vOpInvoiceSrch.InvChargeNonCoComm Charge_Non_Collect_Comm,
                    --Cast(vInvoiceSrch.DocInvDate as float)+2 Calc_DocInvDate,
                    --Cast(vInvoiceSrch.DueDate as float)+2 Calc_DueDate
                     FROM 
                     VVMVOYAGESRCH 
                     JOIN VINVOICESRCH ON VVMVOYAGESRCH.ID = VINVOICESRCH.VOYAGE 
                     JOIN VVMOPFIXSRCH ON VVMVOYAGESRCH.ID = VVMOPFIXSRCH.VMVOYAGENO 
                     JOIN VPROFITCENTREORGANISATIONSRCH ON VVMVOYAGESRCH.ID = VPROFITCENTREORGANISATIONSRCH.ID 
                     JOIN VDEPTORGANISATIONSRCH ON VVMVOYAGESRCH.ID=VDEPTORGANISATIONSRCH.ID 
                     LEFT OUTER JOIN VOPINVOICESRCH ON VVMVOYAGESRCH.ID=VOPINVOICESRCH.VOY AND 
			                     VINVOICESRCH.FIX=VOPINVOICESRCH.FIX AND 
			                     VINVOICESRCH.OPNO=VOPINVOICESRCH.OPNO AND 
			                     VINVOICESRCH.VYNO=VOPINVOICESRCH.VYNO AND 
			                     VINVOICESRCH.GRNO=VOPINVOICESRCH.GRNO AND 
			                     VINVOICESRCH.NO=VOPINVOICESRCH.NO 
                     LEFT OUTER JOIN VOPCHARGEFIXTURESRCH ON VVMOPFIXSRCH.OPSFIXTURE = VOPCHARGEFIXTURESRCH.FIXTURE 
			                     AND  VVMOPFIXSRCH.OPSOPTIONNO = VOPCHARGEFIXTURESRCH.OPSOPTIONNO 
			                     AND  VVMOPFIXSRCH.OPSVOYAGENO = VOPCHARGEFIXTURESRCH.OPSVOYAGENO 
                    -- CROSS JOIN VSSLCLIENTCONFIG 
                     WHERE 
                     vVMVoyageSrch.Category NOT LIKE 'UnFixed' AND 
                     vInvoiceSrch.InvoiceNo <> 'proforma' and
                     vProfitCentreOrganisationSrch.ProfitCentreName <>  'PACCSHIP (UK) LTD' AND
                     vInvoiceSrch.Paid = 0 AND
                     --vInvoiceSrch.DocInvDate > '2012-11-30 23:59' and
                    convert(char, vInvoiceSrch.DocInvDate, 105) = @invoiceDate and
                     vVMVoyageSrch.Type = 'TC Relet'  --//TC Relet
                     --vVMVoyageSrch.Type in ('Cargo Relet','Voyage')   --//type Cargo Relet Voyage 
                     AND vOpInvoiceSrch.Type in ('Commission Credit', 'Commission Debit')
                    AND vOpInvoiceSrch.ChargeType = 'Hire'
                    ";
        }
        public static string GetCommisionInvoiceByVCReletTypeAndInvoiceTypeCommissionByDate()
        {
            return @"
                    Select distinct 
                    vOpInvoiceSrch.Type invoice_type,
                    vInvoiceSrch.Company company,
                    vVMVoyageSrch.VesselName vessel_name,
                    vVMVoyageSrch.Vessel vessel_code,
                    vVMVoyageSrch.VoyageNo voyage_no,
                    vProfitCentreOrganisationSrch.ProfitCentreName profit_centre,
                    vInvoiceSrch.DocInvDate invoice_date,----------
                    vOpInvoiceSrch.Narrative charge_description,
                    vInvoiceSrch.InvoiceNo invoice_number,
                    vInvoiceSrch.InvoiceTotal invoice_amount,
                    vInvoiceSrch.Curr invoice_currency,
                    --vOpchargeFixtureSrch.InvoiceGroup Invoiced,
                    vInvoiceSrch.DueDate invoice_due_date,
                    vOpInvoiceSrch.ChargeType charge_type,
                    vInvoiceSrch.RaisedToFinalBy raised_to_final_by,
                    --vInvoiceSrch.Raised Raised,
                    --vInvoiceSrch.RaisedStatus Raised_Status,
                    --vInvoiceSrch.Paid Paid,
                    --vInvoiceSrch.PaidDate Paid_Date,
                    --vOpInvoiceSrch.InvChargeAddrComm Charge_Addr_Comm,
                    vOpInvoiceSrch.InvChargeAmount charge_amount --For Charge Invoice
                    --vOpInvoiceSrch.InvChargeCoComm Charge_Collect_Comm,
                    --vOpInvoiceSrch.InvChargeNonCoComm Charge_Non_Collect_Comm,
                    --Cast(vInvoiceSrch.DocInvDate as float)+2 Calc_DocInvDate,
                    --Cast(vInvoiceSrch.DueDate as float)+2 Calc_DueDate
                     FROM 
                     VVMVOYAGESRCH 
                     JOIN VINVOICESRCH ON VVMVOYAGESRCH.ID = VINVOICESRCH.VOYAGE 
                     JOIN VVMOPFIXSRCH ON VVMVOYAGESRCH.ID = VVMOPFIXSRCH.VMVOYAGENO 
                     JOIN VPROFITCENTREORGANISATIONSRCH ON VVMVOYAGESRCH.ID = VPROFITCENTREORGANISATIONSRCH.ID 
                     JOIN VDEPTORGANISATIONSRCH ON VVMVOYAGESRCH.ID=VDEPTORGANISATIONSRCH.ID 
                     LEFT OUTER JOIN VOPINVOICESRCH ON VVMVOYAGESRCH.ID=VOPINVOICESRCH.VOY AND 
			                     VINVOICESRCH.FIX=VOPINVOICESRCH.FIX AND 
			                     VINVOICESRCH.OPNO=VOPINVOICESRCH.OPNO AND 
			                     VINVOICESRCH.VYNO=VOPINVOICESRCH.VYNO AND 
			                     VINVOICESRCH.GRNO=VOPINVOICESRCH.GRNO AND 
			                     VINVOICESRCH.NO=VOPINVOICESRCH.NO 
                     LEFT OUTER JOIN VOPCHARGEFIXTURESRCH ON VVMOPFIXSRCH.OPSFIXTURE = VOPCHARGEFIXTURESRCH.FIXTURE 
			                     AND  VVMOPFIXSRCH.OPSOPTIONNO = VOPCHARGEFIXTURESRCH.OPSOPTIONNO 
			                     AND  VVMOPFIXSRCH.OPSVOYAGENO = VOPCHARGEFIXTURESRCH.OPSVOYAGENO 
                    -- CROSS JOIN VSSLCLIENTCONFIG 
                     WHERE 
                     vVMVoyageSrch.Category NOT LIKE 'UnFixed' AND 
                     vInvoiceSrch.InvoiceNo <> 'proforma' and
                     vProfitCentreOrganisationSrch.ProfitCentreName <>  'PACCSHIP (UK) LTD' AND
                     vInvoiceSrch.Paid = 0 AND
                    convert(char, vInvoiceSrch.DocInvDate, 105) = @invoiceDate and
                     vVMVoyageSrch.Type in ('Cargo Relet','Voyage')   --//type Cargo Relet Voyage 
                     AND vOpInvoiceSrch.Type in ('Commission Credit', 'Commission Debit')
                    AND vOpInvoiceSrch.ChargeType <> 'Hire'
                    ";
        }
        public static string GetProformaCommissionCredit()
        {
            return @"
                    select 
	                    VVMVOYAGESRCH.ID,
	                    vInvoiceSrch.Type invoice_type,
	                    vInvoiceSrch.Company company,
	                    vVMVoyageSrch.VesselName vessel_name,
	                    vVMVoyageSrch.Vessel vessel_code,
	                    vVMVoyageSrch.VoyageNo voyage_no,
	                    vProfitCentreOrganisationSrch.ProfitCentreName profit_centre,
	                    vInvoiceSrch.DocInvDate invoice_date,----------
	                    VOPINVOICELINENARRATIVE.Narrative charge_description,
	                    vInvoiceSrch.InvoiceNo invoice_number,
	                    vInvoiceSrch.InvoiceTotal invoice_amount,
	                    vInvoiceSrch.Curr invoice_currency,
	                    vInvoiceSrch.DueDate invoice_due_date,
	                    VOPINVOICELINENARRATIVE.Type charge_type,
	                    vInvoiceSrch.RaisedToFinalBy raised_to_final_by,
	                    vInvoiceSrch.Paid paid,
	                    vInvoiceSrch.PaidDate paid_date,
	                    0 charge_addr_comm,
	                    VOPINVOICELINENARRATIVE.Charge charge_amount, 
	                    0 charge_collect_comm,
                        0 charge_non_collect_comm,
	                    vVMVoyageSrch.Type voyage_type,
	                    VOPCHARGEFIXTURESRCH.CollectComm1 collect_comm,
	                    VOPCHARGEFIXTURESRCH.CollectComm2 collect_comm2,
	                    VOPCHARGEFIXTURESRCH.CollectComm3 collect_comm3,
	                    VOPCHARGEFIXTURESRCH.CollectComm4 collect_comm4,
	                    VOPINVOICELINENARRATIVE.DisplayOrder display_order,
	                    VOPINVOICELINENARRATIVE.ChargeID charge_id,
	                    VOPINVOICELINENARRATIVE.NarrativeLine narrative_line,
	                    VOPCHARGEFIXTURESRCH.Narrative narrative		   			   
                                        from 
                     vInvoiceSrch 
                      join VVMVOYAGESRCH on VVMVOYAGESRCH.ID = VINVOICESRCH.VOYAGE 
                     join VOPINVOICELINENARRATIVE on
                     VOPINVOICELINENARRATIVE.InvoiceNo = VINVOICESRCH.No
                     and  VOPINVOICELINENARRATIVE.Fixture = VINVOICESRCH.Fix and
                                                    VOPINVOICELINENARRATIVE.InvoiceNo = vInvoiceSrch.No and
                                                    VOPINVOICELINENARRATIVE.Charge is not null  and
                                                    VOPINVOICELINENARRATIVE.VoyageNo =  vInvoiceSrch.VyNo and
                                                    VOPINVOICELINENARRATIVE.GroupNo = vInvoiceSrch.GrNo and
                                                    VOPINVOICELINENARRATIVE.OptionNo = vInvoiceSrch.OpNo
                     JOIN VPROFITCENTREORGANISATIONSRCH ON VVMVOYAGESRCH.ID = VPROFITCENTREORGANISATIONSRCH.ID       
                     join VOPCHARGEFIXTURESRCH on VOPCHARGEFIXTURESRCH.ID = VOPINVOICELINENARRATIVE.ChargeID
                    where
                    --vInvoiceSrch.InvoiceNo = 'MBC PADU/202'  
                    VOPINVOICELINENARRATIVE.NarrativeLine in(80)--    Commission CREDIT-- ALL --take collect commision
                    and vInvoiceSrch.DocInvDate > '2013-07-23 00:00'
                    --and vInvoiceSrch.DocInvDate < '2013-12-31 23:59'
                    and VOPINVOICELINENARRATIVE.Charge is not null
                    and vInvoiceSrch.Type in ('Commission Credit', 'Commission Debit') 
                    AND vVMVoyageSrch.Type in ('Cargo Relet','Voyage','TC Relet' )
                    AND vInvoiceSrch.InvoiceNo not like 'PPL%'
                    AND  vVMVoyageSrch.Category NOT LIKE 'UnFixed'  
                    AND  vInvoiceSrch.InvoiceNo = 'proforma'
                    AND  vProfitCentreOrganisationSrch.ProfitCentreName <>  'PACCSHIP (UK) LTD'
                    AND  vProfitCentreOrganisationSrch.ProfitCentreName <> 'PACNAV'
                    --and vVMVoyageSrch.VesselName like '%Jubilant%'
                    --and vVMVoyageSrch.VoyageNo = 14
                     order by 
                    vInvoiceSrch.InvoiceNo,
                    VOPINVOICELINENARRATIVE.DisplayOrder,
                    VOPINVOICELINENARRATIVE.ChargeID,
                    VOPINVOICELINENARRATIVE.NarrativeLine  
";
        }
        public static string GetProformaChargeCredit()
        {
            return @"
                    select 
	                    VVMVOYAGESRCH.ID,
	                    vInvoiceSrch.Type invoice_type,
	                    vInvoiceSrch.Company company,
	                    vVMVoyageSrch.VesselName vessel_name,
	                    vVMVoyageSrch.Vessel vessel_code,
	                    vVMVoyageSrch.VoyageNo voyage_no,
	                    vProfitCentreOrganisationSrch.ProfitCentreName profit_centre,
	                    vInvoiceSrch.DocInvDate invoice_date,----------
	                    VOPINVOICELINENARRATIVE.Narrative charge_description,
	                    vInvoiceSrch.InvoiceNo invoice_number,
	                    vInvoiceSrch.InvoiceTotal invoice_amount,
	                    vInvoiceSrch.Curr invoice_currency,
	                    vInvoiceSrch.DueDate invoice_due_date,
	                    VOPINVOICELINENARRATIVE.Type charge_type,
	                    vInvoiceSrch.RaisedToFinalBy raised_to_final_by,
	                    vInvoiceSrch.Paid paid,
	                    vInvoiceSrch.PaidDate paid_date,
	                    0 charge_addr_comm,
	                    VOPINVOICELINENARRATIVE.Charge charge_amount, 
	                    0 charge_collect_comm,
                        0 charge_non_collect_comm,
	                    vVMVoyageSrch.Type voyage_type,
	                    VOPCHARGEFIXTURESRCH.CollectComm1 collect_comm,
	                    VOPCHARGEFIXTURESRCH.CollectComm2 collect_comm2,
	                    VOPCHARGEFIXTURESRCH.CollectComm3 collect_comm3,
	                    VOPCHARGEFIXTURESRCH.CollectComm4 collect_comm4,
	                    VOPINVOICELINENARRATIVE.DisplayOrder display_order,
	                    VOPINVOICELINENARRATIVE.ChargeID charge_id,
	                    VOPINVOICELINENARRATIVE.NarrativeLine narrative_line,
	                    VOPCHARGEFIXTURESRCH.Narrative narrative		   			   
                                        from 
                     vInvoiceSrch 
                      join VVMVOYAGESRCH on VVMVOYAGESRCH.ID = VINVOICESRCH.VOYAGE 
                     join VOPINVOICELINENARRATIVE on
                     VOPINVOICELINENARRATIVE.InvoiceNo = VINVOICESRCH.No
                     and  VOPINVOICELINENARRATIVE.Fixture = VINVOICESRCH.Fix and
                                                    VOPINVOICELINENARRATIVE.InvoiceNo = vInvoiceSrch.No and
                                                    VOPINVOICELINENARRATIVE.Charge is not null  and
                                                    VOPINVOICELINENARRATIVE.VoyageNo =  vInvoiceSrch.VyNo and
                                                    VOPINVOICELINENARRATIVE.GroupNo = vInvoiceSrch.GrNo and
                                                    VOPINVOICELINENARRATIVE.OptionNo = vInvoiceSrch.OpNo
                     JOIN VPROFITCENTREORGANISATIONSRCH ON VVMVOYAGESRCH.ID = VPROFITCENTREORGANISATIONSRCH.ID       
                     join VOPCHARGEFIXTURESRCH on VOPCHARGEFIXTURESRCH.ID = VOPINVOICELINENARRATIVE.ChargeID
                    where
                    --vInvoiceSrch.InvoiceNo = 'MBC PADU/202'  
                    --VOPINVOICELINENARRATIVE.NarrativeLine not in(80)--    Charge Credit All
                    vInvoiceSrch.DocInvDate > '2013-07-23 00:00'
                    --and vInvoiceSrch.DocInvDate < '2013-12-31 23:59'
                    and VOPINVOICELINENARRATIVE.Charge is not null
                    and vInvoiceSrch.Type in ('Charge Credit') 
                    AND vVMVoyageSrch.Type in ('Cargo Relet','Voyage','TC Relet' )
                    AND vInvoiceSrch.InvoiceNo not like 'PPL%'
                    AND  vVMVoyageSrch.Category NOT LIKE 'UnFixed'  
                    AND  vInvoiceSrch.InvoiceNo = 'proforma'
                    AND  vProfitCentreOrganisationSrch.ProfitCentreName <>  'PACCSHIP (UK) LTD'
                    AND  vProfitCentreOrganisationSrch.ProfitCentreName <> 'PACNAV'
                   -- and vVMVoyageSrch.VesselName like '%Jubilant%'
                    --and vVMVoyageSrch.VoyageNo = 14
                     order by 
                    vInvoiceSrch.InvoiceNo,
                    VOPINVOICELINENARRATIVE.DisplayOrder,
                    VOPINVOICELINENARRATIVE.ChargeID,
                    VOPINVOICELINENARRATIVE.NarrativeLine  
";
        }
        public static string GetProformaChargeDebit()
        {
            return @"
                    select 
	                    VVMVOYAGESRCH.ID,
	                    vInvoiceSrch.Type invoice_type,
	                    vInvoiceSrch.Company company,
	                    vVMVoyageSrch.VesselName vessel_name,
	                    vVMVoyageSrch.Vessel vessel_code,
	                    vVMVoyageSrch.VoyageNo voyage_no,
	                    vProfitCentreOrganisationSrch.ProfitCentreName profit_centre,
	                    vInvoiceSrch.DocInvDate invoice_date,----------
	                    VOPINVOICELINENARRATIVE.Narrative charge_description,
	                    vInvoiceSrch.InvoiceNo invoice_number,
	                    vInvoiceSrch.InvoiceTotal invoice_amount,
	                    vInvoiceSrch.Curr invoice_currency,
	                    vInvoiceSrch.DueDate invoice_due_date,
	                    VOPINVOICELINENARRATIVE.Type charge_type,
	                    vInvoiceSrch.RaisedToFinalBy raised_to_final_by,
	                    vInvoiceSrch.Paid paid,
	                    vInvoiceSrch.PaidDate paid_date,
	                    0 charge_addr_comm,
	                    VOPINVOICELINENARRATIVE.Charge charge_amount, 
	                    0 charge_collect_comm,
                        0 charge_non_collect_comm,
	                    vVMVoyageSrch.Type voyage_type,
	                    VOPCHARGEFIXTURESRCH.CollectComm1 collect_comm,
	                    VOPCHARGEFIXTURESRCH.CollectComm2 collect_comm2,
	                    VOPCHARGEFIXTURESRCH.CollectComm3 collect_comm3,
	                    VOPCHARGEFIXTURESRCH.CollectComm4 collect_comm4,
	                    VOPINVOICELINENARRATIVE.DisplayOrder display_order,
	                    VOPINVOICELINENARRATIVE.ChargeID charge_id,
	                    VOPINVOICELINENARRATIVE.NarrativeLine narrative_line,
	                    VOPCHARGEFIXTURESRCH.Narrative narrative		   			   
                                        from 
                     vInvoiceSrch 
                      join VVMVOYAGESRCH on VVMVOYAGESRCH.ID = VINVOICESRCH.VOYAGE 
                     join VOPINVOICELINENARRATIVE on
                     VOPINVOICELINENARRATIVE.InvoiceNo = VINVOICESRCH.No
                     and  VOPINVOICELINENARRATIVE.Fixture = VINVOICESRCH.Fix and
                                                    VOPINVOICELINENARRATIVE.InvoiceNo = vInvoiceSrch.No and
                                                    VOPINVOICELINENARRATIVE.Charge is not null  and
                                                    VOPINVOICELINENARRATIVE.VoyageNo =  vInvoiceSrch.VyNo and
                                                    VOPINVOICELINENARRATIVE.GroupNo = vInvoiceSrch.GrNo and
                                                    VOPINVOICELINENARRATIVE.OptionNo = vInvoiceSrch.OpNo
                     JOIN VPROFITCENTREORGANISATIONSRCH ON VVMVOYAGESRCH.ID = VPROFITCENTREORGANISATIONSRCH.ID       
                     join VOPCHARGEFIXTURESRCH on VOPCHARGEFIXTURESRCH.ID = VOPINVOICELINENARRATIVE.ChargeID
                    where
                    --vInvoiceSrch.InvoiceNo = 'MBC PADU/202'  
                    VOPINVOICELINENARRATIVE.NarrativeLine not in(80)--    Charge Debit
                    and vInvoiceSrch.DocInvDate > '2013-07-23 00:00'
                    --and vInvoiceSrch.DocInvDate < '2013-12-31 23:59'
                    and VOPINVOICELINENARRATIVE.Charge is not null
                    and vInvoiceSrch.Type in ('Charge Debit') 
                    AND vVMVoyageSrch.Type in ('Cargo Relet','Voyage','TC Relet' )
                    AND vInvoiceSrch.InvoiceNo not like 'PPL%'
                    AND  vVMVoyageSrch.Category NOT LIKE 'UnFixed'  
                    AND  vInvoiceSrch.InvoiceNo = 'proforma'
                    AND  vProfitCentreOrganisationSrch.ProfitCentreName <>  'PACCSHIP (UK) LTD'
                    AND  vProfitCentreOrganisationSrch.ProfitCentreName <> 'PACNAV'
                   -- and vVMVoyageSrch.VesselName like '%Jubilant%'
                    --and vVMVoyageSrch.VoyageNo = 14
                     order by 
                    vInvoiceSrch.InvoiceNo,
                    VOPINVOICELINENARRATIVE.DisplayOrder,
                    VOPINVOICELINENARRATIVE.ChargeID,
                    VOPINVOICELINENARRATIVE.NarrativeLine  
                ";
        }
        public static string GetProforma()
        {
            return @"
                        Select distinct 
                        VVMVOYAGESRCH.ID,
                        vOpInvoiceSrch.Type invoice_type,
                        vInvoiceSrch.Company company,
                        vVMVoyageSrch.VesselName vessel_name,
                        vVMVoyageSrch.Vessel vessel_code,
                        vVMVoyageSrch.VoyageNo voyage_no,
                        vProfitCentreOrganisationSrch.ProfitCentreName profit_centre,
                        vInvoiceSrch.DocInvDate invoice_date,----------
                        vOpInvoiceSrch.Narrative charge_description,
                        vInvoiceSrch.InvoiceNo invoice_number,
                        vInvoiceSrch.InvoiceTotal invoice_amount,
                        vInvoiceSrch.Curr invoice_currency,
                        --vOpchargeFixtureSrch.InvoiceGroup Invoiced,
                        vInvoiceSrch.DueDate invoice_due_date,
                        vOpInvoiceSrch.ChargeType charge_type,
                        vInvoiceSrch.RaisedToFinalBy raised_to_final_by,
                        --vInvoiceSrch.Raised Raised,
                        --vInvoiceSrch.RaisedStatus Raised_Status,
                        vInvoiceSrch.Paid paid,
                        vInvoiceSrch.PaidDate paid_date,
                        vOpInvoiceSrch.InvChargeAddrComm charge_addr_comm,
                        vOpInvoiceSrch.InvChargeAmount charge_amount, --For Charge Invoice
                        vOpInvoiceSrch.InvChargeCoComm charge_collect_comm,
                        vOpInvoiceSrch.InvChargeNonCoComm charge_non_collect_comm,
                        --Cast(vInvoiceSrch.DocInvDate as float)+2 Calc_DocInvDate,
                        --Cast(vInvoiceSrch.DueDate as float)+2 Calc_DueDate
                        vVMVoyageSrch.Type voyage_type
                        ,0 collect_comm
                        ,0 collect_comm2
                        ,0 collect_comm3
                        ,0 collect_comm4
                        --, VOPCHARGEFIXTURESRCH.CollectComm1 collect_comm
                        --,VOPCHARGEFIXTURESRCH.CollectComm2 collect_comm2
                        --,VOPCHARGEFIXTURESRCH.CollectComm3 collect_comm3
                        --,VOPCHARGEFIXTURESRCH.CollectComm4 collect_comm4
                        ,VOPINVOICELINENARRATIVE.DisplayOrder display_order
                        ,VOPINVOICELINENARRATIVE.ChargeID charge_id
                        ,VOPINVOICELINENARRATIVE.NarrativeLine narrative_line
                        FROM 
                        VVMVOYAGESRCH 
                        JOIN VINVOICESRCH ON VVMVOYAGESRCH.ID = VINVOICESRCH.VOYAGE 
                        JOIN VVMOPFIXSRCH ON VVMVOYAGESRCH.ID = VVMOPFIXSRCH.VMVOYAGENO 
                        JOIN VPROFITCENTREORGANISATIONSRCH ON VVMVOYAGESRCH.ID = VPROFITCENTREORGANISATIONSRCH.ID                      
                        LEFT OUTER JOIN VOPINVOICESRCH ON VVMVOYAGESRCH.ID=VOPINVOICESRCH.VOY AND 
                         VINVOICESRCH.FIX=VOPINVOICESRCH.FIX AND 
                         VINVOICESRCH.OPNO=VOPINVOICESRCH.OPNO AND 
                         VINVOICESRCH.VYNO=VOPINVOICESRCH.VYNO AND 
                         VINVOICESRCH.GRNO=VOPINVOICESRCH.GRNO AND 
                         VINVOICESRCH.NO=VOPINVOICESRCH.NO 
                        LEFT OUTER JOIN VOPCHARGEFIXTURESRCH ON VVMOPFIXSRCH.OPSFIXTURE = VOPCHARGEFIXTURESRCH.FIXTURE 
                         AND  VVMOPFIXSRCH.OPSOPTIONNO = VOPCHARGEFIXTURESRCH.OPSOPTIONNO 
                         AND  VVMOPFIXSRCH.OPSVOYAGENO = VOPCHARGEFIXTURESRCH.OPSVOYAGENO 
                         AND  VOPCHARGEFIXTURESRCH.Reversal = 0 
                         and VOPCHARGEFIXTURESRCH.Reversed = 0 
                         and VOPCHARGEFIXTURESRCH.Narrative = vOpInvoiceSrch.Narrative
                        LEFT OUTER JOIN VOPINVOICELINENARRATIVE ON
                        VOPINVOICELINENARRATIVE.ChargeID = vOpInvoiceSrch.ChargeId  and 
                        VOPINVOICELINENARRATIVE.InvoiceNo = VINVOICESRCH.No and
                        VOPINVOICELINENARRATIVE.Charge is not null and              
                        VOPINVOICELINENARRATIVE.NarrativeLine not in(70,80,90)
                        -- CROSS JOIN VSSLCLIENTCONFIG 
                        WHERE 
                        vInvoiceSrch.DocInvDate > '2013-07-23 00:00' AND
                        vVMVoyageSrch.Category NOT LIKE 'UnFixed' AND 
                        vInvoiceSrch.InvoiceNo = 'proforma'--AND
                        --(vVMVoyageSrch.VesselName like '%alam bakti%')  
                        --and vOpInvoiceSrch.Type = 'Commission Credit'
                        order by 
                        invoice_date,
                        voyage_no,                    
                        vessel_name,
                        company,     
                        invoice_amount,               
                        VOPINVOICELINENARRATIVE.DisplayOrder,
                        VOPINVOICELINENARRATIVE.ChargeID,
                        VOPINVOICELINENARRATIVE.NarrativeLine
            ";
        }
        public static string GetFreightHireInvoiceByDate()
        {
            return @"
                    Select distinct 
                    vOpInvoiceSrch.Type invoice_type,
                    vInvoiceSrch.Company company,
                    vVMVoyageSrch.VesselName vessel_name,
                    vVMVoyageSrch.Vessel vessel_code,
                    vVMVoyageSrch.VoyageNo voyage_no,
                    vProfitCentreOrganisationSrch.ProfitCentreName profit_centre,
                    vInvoiceSrch.DocInvDate invoice_date,----------
                    vOpInvoiceSrch.Narrative charge_description,
                    vInvoiceSrch.InvoiceNo invoice_number,
                    vInvoiceSrch.InvoiceTotal invoice_amount,
                    vInvoiceSrch.Curr invoice_currency,
                    --vOpchargeFixtureSrch.InvoiceGroup Invoiced,
                    vInvoiceSrch.DueDate invoice_due_date,
                    vOpInvoiceSrch.ChargeType charge_type,
                    vInvoiceSrch.RaisedToFinalBy raised_to_final_by,
                    --vInvoiceSrch.Raised Raised,
                    --vInvoiceSrch.RaisedStatus Raised_Status,
                    --vInvoiceSrch.Paid Paid,
                    --vInvoiceSrch.PaidDate Paid_Date,
                    --vOpInvoiceSrch.InvChargeAddrComm Charge_Addr_Comm,
                    vOpInvoiceSrch.InvChargeAmount charge_amount --For Charge Invoice
                    --vOpInvoiceSrch.InvChargeCoComm Charge_Collect_Comm,
                    --vOpInvoiceSrch.InvChargeNonCoComm Charge_Non_Collect_Comm,
                    --Cast(vInvoiceSrch.DocInvDate as float)+2 Calc_DocInvDate,
                    --Cast(vInvoiceSrch.DueDate as float)+2 Calc_DueDate
                     FROM 
                     VVMVOYAGESRCH 
                     JOIN VINVOICESRCH ON VVMVOYAGESRCH.ID = VINVOICESRCH.VOYAGE 
                     JOIN VVMOPFIXSRCH ON VVMVOYAGESRCH.ID = VVMOPFIXSRCH.VMVOYAGENO 
                     JOIN VPROFITCENTREORGANISATIONSRCH ON VVMVOYAGESRCH.ID = VPROFITCENTREORGANISATIONSRCH.ID 
                     JOIN VDEPTORGANISATIONSRCH ON VVMVOYAGESRCH.ID=VDEPTORGANISATIONSRCH.ID 
                     LEFT OUTER JOIN VOPINVOICESRCH ON VVMVOYAGESRCH.ID=VOPINVOICESRCH.VOY AND 
			                     VINVOICESRCH.FIX=VOPINVOICESRCH.FIX AND 
			                     VINVOICESRCH.OPNO=VOPINVOICESRCH.OPNO AND 
			                     VINVOICESRCH.VYNO=VOPINVOICESRCH.VYNO AND 
			                     VINVOICESRCH.GRNO=VOPINVOICESRCH.GRNO AND 
			                     VINVOICESRCH.NO=VOPINVOICESRCH.NO 
                     LEFT OUTER JOIN VOPCHARGEFIXTURESRCH ON VVMOPFIXSRCH.OPSFIXTURE = VOPCHARGEFIXTURESRCH.FIXTURE 
			                     AND  VVMOPFIXSRCH.OPSOPTIONNO = VOPCHARGEFIXTURESRCH.OPSOPTIONNO 
			                     AND  VVMOPFIXSRCH.OPSVOYAGENO = VOPCHARGEFIXTURESRCH.OPSVOYAGENO 
                    -- CROSS JOIN VSSLCLIENTCONFIG 
                     WHERE 
                     vVMVoyageSrch.Category NOT LIKE 'UnFixed' AND 
                     vInvoiceSrch.InvoiceNo <> 'proforma' and
                     vProfitCentreOrganisationSrch.ProfitCentreName <>  'PACCSHIP (UK) LTD' AND
                     vInvoiceSrch.Paid = 0 AND
                    convert(char, vInvoiceSrch.DocInvDate, 105) = @invoiceDate 
                     AND vOpInvoiceSrch.Type in ('Charge Credit', 'Charge Debit')
                     --AND vOpInvoiceSrch.ChargeType in ('Hire','Freight','Cargoes')
                    ";
        }
        public static string GetFreightHireInvoiceChargeDebit()
        {
            return @"
 select 
	                    VVMVOYAGESRCH.ID,
	                    vInvoiceSrch.Type invoice_type,
	                    vInvoiceSrch.Company company,
	                    vVMVoyageSrch.VesselName vessel_name,
	                    vVMVoyageSrch.Vessel vessel_code,
	                    vVMVoyageSrch.VoyageNo voyage_no,
	                    vProfitCentreOrganisationSrch.ProfitCentreName profit_centre,
	                    vInvoiceSrch.DocInvDate invoice_date,----------
	                    VOPINVOICELINENARRATIVE.Narrative charge_description,
	                    vInvoiceSrch.InvoiceNo invoice_number,
	                    vInvoiceSrch.InvoiceTotal invoice_amount,
	                    vInvoiceSrch.Curr invoice_currency,
	                    vInvoiceSrch.DueDate invoice_due_date,
	                    VOPINVOICELINENARRATIVE.Type charge_type,
	                    vInvoiceSrch.RaisedToFinalBy raised_to_final_by,
	                    vInvoiceSrch.Paid paid,
	                    vInvoiceSrch.PaidDate paid_date,
	                    0 charge_addr_comm,
	                    VOPINVOICELINENARRATIVE.Charge charge_amount, 
	                    0 charge_collect_comm,
                        0 charge_non_collect_comm,
	                    vVMVoyageSrch.Type voyage_type,
	                    VOPCHARGEFIXTURESRCH.CollectComm1 collect_comm,
	                    VOPCHARGEFIXTURESRCH.CollectComm2 collect_comm2,
	                    VOPCHARGEFIXTURESRCH.CollectComm3 collect_comm3,
	                    VOPCHARGEFIXTURESRCH.CollectComm4 collect_comm4,
	                    VOPINVOICELINENARRATIVE.DisplayOrder display_order,
	                    VOPINVOICELINENARRATIVE.ChargeID charge_id,
	                    VOPINVOICELINENARRATIVE.NarrativeLine narrative_line,
	                    VOPCHARGEFIXTURESRCH.Narrative narrative		   		   
                                        from 
                     vInvoiceSrch 
                      join VVMVOYAGESRCH on VVMVOYAGESRCH.ID = VINVOICESRCH.VOYAGE 
                     join VOPINVOICELINENARRATIVE on
                     VOPINVOICELINENARRATIVE.InvoiceNo = VINVOICESRCH.No
                     and  VOPINVOICELINENARRATIVE.Fixture = VINVOICESRCH.Fix and
                                                    VOPINVOICELINENARRATIVE.InvoiceNo = vInvoiceSrch.No and
                                                    VOPINVOICELINENARRATIVE.Charge is not null  and
                                                    VOPINVOICELINENARRATIVE.VoyageNo =  vInvoiceSrch.VyNo and
                                                    VOPINVOICELINENARRATIVE.GroupNo = vInvoiceSrch.GrNo and
                                                    VOPINVOICELINENARRATIVE.OptionNo = vInvoiceSrch.OpNo
                     JOIN VPROFITCENTREORGANISATIONSRCH ON VVMVOYAGESRCH.ID = VPROFITCENTREORGANISATIONSRCH.ID       
                     join VOPCHARGEFIXTURESRCH on VOPCHARGEFIXTURESRCH.ID = VOPINVOICELINENARRATIVE.ChargeID
                    where
                    --vInvoiceSrch.InvoiceNo = 'MBC PADU/202'  
                    VOPINVOICELINENARRATIVE.NarrativeLine not in(80)--    charge debit--not 80
                    and vInvoiceSrch.DocInvDate > '2013-07-23 00:00'
                    and VOPINVOICELINENARRATIVE.Charge is not null
                    and vInvoiceSrch.Type in ('Charge Debit') 
                    AND vVMVoyageSrch.Type in ('Cargo Relet','Voyage','TC Relet' )
                    AND vInvoiceSrch.InvoiceNo not like 'PPL%'
                    AND  vVMVoyageSrch.Category NOT LIKE 'UnFixed'  
                    AND  vInvoiceSrch.InvoiceNo <> 'proforma'
                    AND  vProfitCentreOrganisationSrch.ProfitCentreName <>  'PACCSHIP (UK) LTD'
                     AND  vProfitCentreOrganisationSrch.ProfitCentreName <> 'PACNAV'  
                    OR
                    (
                    VOPINVOICELINENARRATIVE.NarrativeLine not in(80)--    charge debit--not 80
                    and vInvoiceSrch.DocInvDate > '2014-01-01 00:00'
                    and VOPINVOICELINENARRATIVE.Charge is not null
                    and vInvoiceSrch.Type in ('Charge Debit') 
                    AND vVMVoyageSrch.Type in ('Cargo Relet','Voyage','TC Relet' )
                    AND vInvoiceSrch.InvoiceNo not like 'PPL%'
                    AND  vVMVoyageSrch.Category NOT LIKE 'UnFixed'  
                    AND  vInvoiceSrch.InvoiceNo <> 'proforma'
                    AND  vProfitCentreOrganisationSrch.ProfitCentreName =  'PACCSHIP (UK) LTD'
                    )                
                    order by 
                    vInvoiceSrch.InvoiceNo,
                    VOPINVOICELINENARRATIVE.DisplayOrder,
                    VOPINVOICELINENARRATIVE.ChargeID,
                    VOPINVOICELINENARRATIVE.NarrativeLine  
";
        }
        public static string GetFreightHireInvoiceChargeCredit()
        {
            return @"
 select 
	                    VVMVOYAGESRCH.ID,
	                    vInvoiceSrch.Type invoice_type,
	                    vInvoiceSrch.Company company,
	                    vVMVoyageSrch.VesselName vessel_name,
	                    vVMVoyageSrch.Vessel vessel_code,
	                    vVMVoyageSrch.VoyageNo voyage_no,
	                    vProfitCentreOrganisationSrch.ProfitCentreName profit_centre,
	                    vInvoiceSrch.DocInvDate invoice_date,----------
	                    VOPINVOICELINENARRATIVE.Narrative charge_description,
	                    vInvoiceSrch.InvoiceNo invoice_number,
	                    vInvoiceSrch.InvoiceTotal invoice_amount,
	                    vInvoiceSrch.Curr invoice_currency,
	                    vInvoiceSrch.DueDate invoice_due_date,
	                    VOPINVOICELINENARRATIVE.Type charge_type,
	                    vInvoiceSrch.RaisedToFinalBy raised_to_final_by,
	                    vInvoiceSrch.Paid paid,
	                    vInvoiceSrch.PaidDate paid_date,
	                    0 charge_addr_comm,
	                    VOPINVOICELINENARRATIVE.Charge charge_amount, 
	                    0 charge_collect_comm,
                        0 charge_non_collect_comm,
	                    vVMVoyageSrch.Type voyage_type,
	                    VOPCHARGEFIXTURESRCH.CollectComm1 collect_comm,
	                    VOPCHARGEFIXTURESRCH.CollectComm2 collect_comm2,
	                    VOPCHARGEFIXTURESRCH.CollectComm3 collect_comm3,
	                    VOPCHARGEFIXTURESRCH.CollectComm4 collect_comm4,
	                    VOPINVOICELINENARRATIVE.DisplayOrder display_order,
	                    VOPINVOICELINENARRATIVE.ChargeID charge_id,
	                    VOPINVOICELINENARRATIVE.NarrativeLine narrative_line,
	                    VOPCHARGEFIXTURESRCH.Narrative narrative		   		   
                                        from 
                     vInvoiceSrch 
                      join VVMVOYAGESRCH on VVMVOYAGESRCH.ID = VINVOICESRCH.VOYAGE 
                     join VOPINVOICELINENARRATIVE on
                     VOPINVOICELINENARRATIVE.InvoiceNo = VINVOICESRCH.No
                     and  VOPINVOICELINENARRATIVE.Fixture = VINVOICESRCH.Fix and
                                                    VOPINVOICELINENARRATIVE.InvoiceNo = vInvoiceSrch.No and
                                                    VOPINVOICELINENARRATIVE.Charge is not null  and
                                                    VOPINVOICELINENARRATIVE.VoyageNo =  vInvoiceSrch.VyNo and
                                                    VOPINVOICELINENARRATIVE.GroupNo = vInvoiceSrch.GrNo and
                                                    VOPINVOICELINENARRATIVE.OptionNo = vInvoiceSrch.OpNo
                     JOIN VPROFITCENTREORGANISATIONSRCH ON VVMVOYAGESRCH.ID = VPROFITCENTREORGANISATIONSRCH.ID       
                     join VOPCHARGEFIXTURESRCH on VOPCHARGEFIXTURESRCH.ID = VOPINVOICELINENARRATIVE.ChargeID
                    where
                    --vInvoiceSrch.InvoiceNo = 'MBC PADU/202'  
                    --VOPINVOICELINENARRATIVE.NarrativeLine not in(80)--    Charge CREDIT-- ALL
                    vInvoiceSrch.DocInvDate > '2013-07-23 00:00'
                    and VOPINVOICELINENARRATIVE.Charge is not null
                    and vInvoiceSrch.Type in ('Charge Credit') 
                    AND vVMVoyageSrch.Type in ('Cargo Relet','Voyage','TC Relet' )
                    AND vInvoiceSrch.InvoiceNo not like 'PPL%'
                    AND  vVMVoyageSrch.Category NOT LIKE 'UnFixed'  
                    AND  vInvoiceSrch.InvoiceNo <> 'proforma'
                    AND  vProfitCentreOrganisationSrch.ProfitCentreName <>  'PACCSHIP (UK) LTD'
                     AND  vProfitCentreOrganisationSrch.ProfitCentreName <> 'PACNAV'
                    OR
                    (
                    vInvoiceSrch.DocInvDate > '2014-01-01 00:00'
                    and VOPINVOICELINENARRATIVE.Charge is not null
                    and vInvoiceSrch.Type in ('Charge Credit') 
                    AND vVMVoyageSrch.Type in ('Cargo Relet','Voyage','TC Relet' )
                    AND vInvoiceSrch.InvoiceNo not like 'PPL%'
                    AND  vVMVoyageSrch.Category NOT LIKE 'UnFixed'  
                    AND  vInvoiceSrch.InvoiceNo <> 'proforma'
                    AND  vProfitCentreOrganisationSrch.ProfitCentreName =  'PACCSHIP (UK) LTD'
                    )
                     order by 
                    vInvoiceSrch.InvoiceNo,
                    VOPINVOICELINENARRATIVE.DisplayOrder,
                    VOPINVOICELINENARRATIVE.ChargeID,
                    VOPINVOICELINENARRATIVE.NarrativeLine  
";
        }
        public static string GetFreightHireInvoice()
        {
            return @"
                   Select  distinct
                    VVMVOYAGESRCH.ID,
                    vOpInvoiceSrch.Type invoice_type,
                    vInvoiceSrch.Company company,
                    vVMVoyageSrch.VesselName vessel_name,
                    vVMVoyageSrch.Vessel vessel_code,
                    vVMVoyageSrch.VoyageNo voyage_no,
                    vProfitCentreOrganisationSrch.ProfitCentreName profit_centre,
                    vInvoiceSrch.DocInvDate invoice_date,----------
                    vOpInvoiceSrch.Narrative charge_description,
                    vInvoiceSrch.InvoiceNo invoice_number,
                    vInvoiceSrch.InvoiceTotal invoice_amount,
                    vInvoiceSrch.Curr invoice_currency,
                    --vOpchargeFixtureSrch.InvoiceGroup Invoiced,
                    vInvoiceSrch.DueDate invoice_due_date,
                    vOpInvoiceSrch.ChargeType charge_type,
                    vInvoiceSrch.RaisedToFinalBy raised_to_final_by,
                    --vInvoiceSrch.Raised Raised,
                    --vInvoiceSrch.RaisedStatus Raised_Status,
                    vInvoiceSrch.Paid paid,
                    vInvoiceSrch.PaidDate paid_date,
                    vOpInvoiceSrch.InvChargeAddrComm charge_addr_comm,
                    vOpInvoiceSrch.InvChargeAmount charge_amount, --For Charge Invoice
                    vOpInvoiceSrch.InvChargeCoComm charge_collect_comm,
                    vOpInvoiceSrch.InvChargeNonCoComm charge_non_collect_comm,
                    --Cast(vInvoiceSrch.DocInvDate as float)+2 Calc_DocInvDate,
                    --Cast(vInvoiceSrch.DueDate as float)+2 Calc_DueDate
                    vVMVoyageSrch.Type voyage_type
                    ,0 collect_comm
                    ,0 collect_comm2
                    ,0 collect_comm3
                    ,0 collect_comm4
                    ,VOPINVOICELINENARRATIVE.DisplayOrder display_order
                    ,VOPINVOICELINENARRATIVE.ChargeID charge_id
                    ,VOPINVOICELINENARRATIVE.NarrativeLine narrative_line
                     FROM 
                     VVMVOYAGESRCH 
                     JOIN VINVOICESRCH ON VVMVOYAGESRCH.ID = VINVOICESRCH.VOYAGE                      
                     JOIN VPROFITCENTREORGANISATIONSRCH ON VVMVOYAGESRCH.ID = VPROFITCENTREORGANISATIONSRCH.ID                      
                     LEFT OUTER JOIN VOPINVOICESRCH ON VVMVOYAGESRCH.ID=VOPINVOICESRCH.VOY AND 
			                     VINVOICESRCH.FIX=VOPINVOICESRCH.FIX AND 
			                     VINVOICESRCH.OPNO=VOPINVOICESRCH.OPNO AND 
			                     VINVOICESRCH.VYNO=VOPINVOICESRCH.VYNO AND 
			                     VINVOICESRCH.GRNO=VOPINVOICESRCH.GRNO AND 
			                     VINVOICESRCH.NO=VOPINVOICESRCH.NO   
                     LEFT OUTER JOIN VOPINVOICELINENARRATIVE ON
                                VOPINVOICELINENARRATIVE.ChargeID = vOpInvoiceSrch.ChargeId  and 
                                VOPINVOICELINENARRATIVE.InvoiceNo = VINVOICESRCH.No and
                                VOPINVOICELINENARRATIVE.Charge is not null and              
                                VOPINVOICELINENARRATIVE.NarrativeLine not in(70,80,90)                     
                    -- CROSS JOIN VSSLCLIENTCONFIG 
                     WHERE 
                     vVMVoyageSrch.Category NOT LIKE 'UnFixed' AND 
                     vInvoiceSrch.InvoiceNo <> 'proforma' and
                     vProfitCentreOrganisationSrch.ProfitCentreName <>  'PACCSHIP (UK) LTD' AND
                    -- vInvoiceSrch.Paid = 0 AND
                     vInvoiceSrch.DocInvDate > '2013-07-23 00:00' AND
                     vOpInvoiceSrch.Type in ('Charge Credit', 'Charge Debit') AND
                    vInvoiceSrch.InvoiceNo not like 'PPL%'
                    --AND vInvoiceSrch.InvoiceNo = 'PCLS/8314'  
                     order by 
                    vInvoiceSrch.InvoiceNo,
                    VOPINVOICELINENARRATIVE.DisplayOrder,
                    VOPINVOICELINENARRATIVE.ChargeID,
                    VOPINVOICELINENARRATIVE.NarrativeLine  
                    ";
        }
        public static string CountCommissionInvoiceTemp()
        {
            return @"
            select count(*) from i_commission_invoice
            where [invoice_number]= @invoice_number   
            ";
            //and [charge_description] = @charge_description
            //and [charge_type]= @charge_type
            //  and [charge_amount] = @charge_amount
            //  and  charge_addr_comm = @charge_addr_comm
            //   and  charge_collect_comm= @charge_collect_comm
            //   and  charge_non_collect_comm= @charge_non_collect_comm         


            //and [invoice_type] = @invoice_type
            //  and [company]= @company
            //  and [vessel_name]= @vessel_name
            //  and [vessel_code]= @vessel_code
            //  and [voyage_no]= @voyage_no
            //  and [profit_centre]= @profit_centre
            //  and [invoice_date] = @invoice_date

            //        and [invoice_amount] = @invoice_amount
            //  and [invoice_currency] = @invoice_currency
            //  and [invoice_due_date]= @invoice_due_date
            // 
            //  and [raised_to_final_by] = @raised_to_final_by

            //   and  voyage_type= @voyage_type
            //   AND [collect_comm] = @collect_comm
            //   AND [collect_comm2] = @collect_comm2
            //   AND [collect_comm3] = @collect_comm3
            //   AND [collect_comm4] = @collect_comm4
        }
        public static string GetAllDistinctProformaInvoice()
        {
            return @"select distinct [company], [vessel_name],[voyage_no],[invoice_date],[invoice_amount],invoice_type from i_commission_invoice
where invoice_number = 'Proforma' AND pickup_flag in (0,1,2) ";
        }
        public static string CountCommissionInvoiceTempProforma()
        {
            return @"
            select count(*) from i_commission_invoice
            where [company]= @company  and [vessel_name]= @vessel_name  and [voyage_no]= @voyage_no 
            and [invoice_date] = @invoice_date  and [invoice_amount] = @invoice_amount and [charge_type]= @charge_type";

            //[invoice_number]= @invoice_number";
            //and [invoice_type] = @invoice_type
            //  and 
            // 
            //  and [vessel_code]= @vessel_code
            // 
            //  and [profit_centre]= @profit_centre
            // 
            //  and [charge_description] = @charge_description
            //       
            //  and [invoice_currency] = @invoice_currency
            //  and [invoice_due_date]= @invoice_due_date
            //  
            //  and [raised_to_final_by] = @raised_to_final_by
            //  and [charge_amount] = @charge_amount
            //   and  charge_addr_comm = @charge_addr_comm
            //   and  charge_collect_comm= @charge_collect_comm
            //   and  charge_non_collect_comm= @charge_non_collect_comm
            //   and  voyage_type= @voyage_type
            //   AND [collect_comm] = @collect_comm
            //   AND [collect_comm2] = @collect_comm2
            //   AND [collect_comm3] = @collect_comm3
            //   AND [collect_comm4] = @collect_comm4
        }
        public static string CommissionInvoiceTemp()
        {
            return @"
            select commission_invoice_id from i_commission_invoice
            where
                [invoice_type] = @invoice_type
               and [company]= @company
               and [vessel_name]= @vessel_name
               and [vessel_code]= @vessel_code
               and [voyage_no]= @voyage_no
               and [profit_centre]= @profit_centre
               and [invoice_date] = @invoice_date
               and [charge_description] = @charge_description
               and [invoice_number]= @invoice_number
               and [invoice_amount] = @invoice_amount
               and [invoice_currency] = @invoice_currency
               and [invoice_due_date]= @invoice_due_date
               and [charge_type]= @charge_type
               and [raised_to_final_by] = @raised_to_final_by
               and [charge_amount] = @charge_amount
                --and paid=@paid
                --and paid_date=@paid_date
                and  charge_addr_comm = @charge_addr_comm
                and  charge_collect_comm= @charge_collect_comm
                and  charge_non_collect_comm= @charge_non_collect_comm
                and  voyage_type= @voyage_type
                AND [collect_comm] = @collect_comm
                AND [collect_comm2] = @collect_comm2
                AND [collect_comm3] = @collect_comm3
                AND [collect_comm4] = @collect_comm4
            ";
        }
        public static string UpdateCommissionInvoiceTemp()
        {
            return @"
            update i_commission_invoice set 
            display_order = @display_order
            ,charge_id = @charge_id
            ,narrative_line = @narrative_line
            where commission_invoice_id=@commission_invoice_id
            ";
        }
        public static string InsertCommissionInvoiceTemp()
        {
            return @"
INSERT INTO [i_commission_invoice]
           ([invoice_type]
           ,[company]
           ,[vessel_name]
           ,[vessel_code]
           ,[voyage_no]
           ,[profit_centre]
           ,[invoice_date]
           ,[charge_description]
           ,[invoice_number]
           ,[invoice_amount]
           ,[invoice_currency]
           ,[invoice_due_date]
           ,[charge_type]
           ,[raised_to_final_by]
           ,[charge_amount]
           ,[pickup_flag]
            ,[insert_date]
            ,paid
           -- ,paid_date
            ,charge_addr_comm
            ,charge_collect_comm
            ,charge_non_collect_comm
            ,voyage_type
            ,collect_comm
            ,collect_comm2
            ,collect_comm3
            ,collect_comm4
            ,display_order
            ,charge_id
            ,narrative_line
            ,ID
            ,narrative
            )
     VALUES
           (@invoice_type
           ,@company
           ,@vessel_name
           ,@vessel_code
           ,@voyage_no
           ,@profit_centre
           ,@invoice_date
           ,@charge_description
           ,@invoice_number
           ,@invoice_amount
           ,@invoice_currency
           ,@invoice_due_date
           ,@charge_type
           ,@raised_to_final_by
           ,@charge_amount
           ,@pickup_flag 
            ,@insert_date
            ,@paid
          --  ,@paid_date
            ,@charge_addr_comm
            ,@charge_collect_comm
            ,@charge_non_collect_comm
            ,@voyage_type
            ,@collect_comm
            ,@collect_comm2
            ,@collect_comm3
            ,@collect_comm4
            ,@display_order
            ,@charge_id
            ,@narrative_line
            ,@ID
            ,@narrative
)
            ";
        }

        public static string GetCommisionInvoiceByTCReletTypeAndInvoiceTypeCommissionFromInterface()
        {
            return @"
            select * from i_commission_invoice
            where  invoice_type in ('Commission Credit', 'Commission Debit')
                    AND charge_type = 'Hire'"
                ;
        }
        public static string GetCommisionInvoiceByTCReletTypeAndInvoiceTypeCommissionFromInterfacePending()
        {
            return @"
            select * from i_commission_invoice
            where  invoice_type in ('Commission Credit', 'Commission Debit')
                    AND charge_type = 'Hire' AND pickup_flag <> '1' AND voyage_type = 'TC Relet'"
                ;
        }
        public static string GetCommisionInvoiceByTCReletTypeAndInvoiceTypeCommissionFromInterfacePendingByInvoiceNumber()
        {
            return @"
            select * from i_commission_invoice
            where  invoice_type in ('Commission Credit', 'Commission Debit')
                    --AND charge_type = 'Hire'
                    AND pickup_flag = '0' 
                    ---AND voyage_type = 'TC Relet'
                    and invoice_number = @invoice_number
                    and invoice_number <> 'Proforma'
                     order by display_order,
                    charge_id,
                    narrative_line"
                ;
        }

        public static string GetCommisionInvoiceByTCReletTypeAndInvoiceTypeChargeDebitFromInterfacePendingByInvoiceNumber()
        {
            return @"
            select * from i_commission_invoice
            where  invoice_type in ('Charge Debit')
                    --AND charge_type = 'Hire'
                    AND pickup_flag = '0' 
                    AND voyage_type in ('TC Relet','Voyage')
                    and invoice_number = @invoice_number
                    and invoice_number <> 'Proforma'
                     order by display_order,
                    charge_id,
                    narrative_line"
                ;
        }

        public static string GetCommisionInvoiceInvoiceNumberByTCReletTypeAndInvoiceTypeCommissionFromInterfacePending()
        {
            return @"
          select  distinct(invoice_number) from i_commission_invoice
            where  invoice_type in ('Commission Credit')
                    --AND charge_type = 'Hire'
                    AND pickup_flag = '0' 
                    AND invoice_number <> 'Proforma'
                    AND voyage_type in ('TC Relet', 'Voyage') --> ALL Commission Credit //13092013
            "
                ;
        }
        public static string GetCommisionInvoiceInvoiceNumberByTCReletTypeAndInvoiceTypeChargeDebitFromInterfacePending()
        {
            return @"
          select  distinct(invoice_number) from i_commission_invoice
            where  invoice_type in ('Charge Debit')
                    --AND charge_type = 'Hire'
                    AND pickup_flag = '0' 
                    AND invoice_number <> 'Proforma'
                    AND voyage_type in ('TC Relet', 'Voyage')--> ALL Commission Credit //13092013
            "
                ;
        }
        public static string GetCommisionInvoiceByVCReletTypeAndInvoiceTypeCommissionFromInterfacePending()
        {
            return @"
            select * from i_commission_invoice
            where  invoice_type in ('Commission Credit', 'Commission Debit')
                    --AND charge_type <> 'Hire' 
                    AND pickup_flag <> '1' AND voyage_type in ('Cargo Relet','Voyage')"
                ;
        }
        public static string GetCommisionInvoiceInvoiceNumberByVCReletTypeAndInvoiceTypeCommissionFromInterfacePending()
        {
            return @"
            select  distinct(invoice_number) from i_commission_invoice
            where  invoice_type in ('Commission Credit', 'Commission Debit')
                    --AND charge_type <> 'Hire' 
                    AND pickup_flag <> '1' AND voyage_type in ('Cargo Relet','Voyage')"
                ;
        }
        public static string GetCommisionInvoiceByVCReletTypeAndInvoiceTypeCommissionFromInterfacePendingByInvoiceNumber()
        {
            return @"
            select * from i_commission_invoice
            where  invoice_type in ('Commission Credit', 'Commission Debit')
                    --AND charge_type <> 'Hire' 
                    AND pickup_flag <> '1' AND voyage_type in ('Cargo Relet','Voyage')
                    AND invoice_number = @invoice_number"
                ;
        }
        public static string GetFreightAndHireFromInterfacePending()
        {
            return @"
            select * from i_commission_invoice
            where  invoice_type in ('Charge Credit', 'Charge Debit')
                    AND pickup_flag <> '1'"
                ;
        }
        public static string GetInvoiceNumberFreightAndHireINChargeDebitFromInterfacePending()
        {
            return @"
            select distinct(invoice_number) from i_commission_invoice
            where   pickup_flag = '0' 
                    AND invoice_number <> 'Proforma'  
                    AND voyage_type = 'Cargo Relet' 
                    AND invoice_type = 'Charge Debit'
                    "
                ;
            //invoice_type in ('Charge Credit', 'Charge Debit')
            //        AND
        }

        public static string GetInvoiceNumberFreightAndHireINCommissionCreditFromInterfacePending()
        {
            return @"
            select distinct(invoice_number) from i_commission_invoice
            where   pickup_flag = '0' 
                    AND invoice_number <> 'Proforma'  
                    AND voyage_type = 'Cargo Relet' 
                    AND invoice_type = 'Commission Credit'
                    "
                ;
        }

        public static string GetInvoiceNumberFreightAndHireOUTFromInterfacePending()
        {
            return @"
            select distinct(invoice_number) from i_commission_invoice
            where  --invoice_type in ('Charge Credit', 'Charge Debit')
                    --AND 
                    pickup_flag = '0' 
                    AND invoice_number <> 'Proforma' 
                    AND voyage_type = 'Cargo Relet' 
                    AND invoice_type = 'Charge Credit'"
                ;
        }

        public static string GetFreightAndHireFromInterfacePendingByInvoiceNumber()
        {
            //select * from i_commission_invoice
            return @"          
                select * from i_commission_Invoice 
            where  invoice_type in ('Charge Credit', 'Charge Debit')
                    AND pickup_flag = '0'
                    AND invoice_number = @invoice_number
                    and invoice_number <> 'Proforma'
                    order by display_order,
                    charge_id,
                    narrative_line
            "
                ;
        }

        public static string GetFreightAndHireFromCommissionCreditInterfacePendingByInvoiceNumber()
        {
            //select * from i_commission_invoice
            return @"          
                select * from i_commission_Invoice 
            where  invoice_type in ('Commission Credit')
                    AND pickup_flag = '0'
                    AND invoice_number = @invoice_number
                    and invoice_number <> 'Proforma' 
                    order by display_order,
                    charge_id,
                    narrative_line
            "
                ;
        }

        public static string GetCommisionInvoiceByTCReletTypeAndInvoiceTypeCommissionFromInterfaceByDate()
        {
            return @"
            select * from i_commission_invoice
            where  invoice_type in ('Commission Credit', 'Commission Debit')
                    AND charge_type = 'Hire'
                    AND convert(char, invoice_date, 105) = @invoice_date 
                ";
        }
        public static string GetCommisionInvoiceByTCReletTypeAndInvoiceTypeCommissionFromInterfaceByDatePending()
        {
            return @"
            select * from i_commission_invoice
            where  invoice_type in ('Commission Credit', 'Commission Debit')
                    AND charge_type = 'Hire'
                    AND convert(char, invoice_date, 105) = @invoice_date 
                    AND pickup_flag <> '1'";
        }

        public static string UpdateCommissionInvoiceTempPickup_flagStatus()
        {
            return @"update i_commission_invoice
            set 
            pickup_flag = @pickup_flag 
            , pickup_date = @pickup_date 
            , pickup_update_date=@pickup_update_date
            , agrtidList = @agrtidList
            , remarks = @remarks
            , batch_id = @batch_id
            where commission_invoice_id=@commission_invoice_id
               ";
        }
        public static string UpdateCommissionInvoiceTempPickup_flagStatusOnly()
        {
            return @"update i_commission_invoice
            set 
            pickup_flag = @pickup_flag 
            where commission_invoice_id=@commission_invoice_id
               ";
        }

        public static string GetAllPendingTCReletCommission()
        {
            return @"select distinct(invoice_number), invoice_date, convert(char, pickup_date, 101) pickup_date, remarks from i_commission_invoice 
                where invoice_type in ('Commission Credit','Commission Debit', 'Charge Debit') and pickup_flag = 0 and invoice_number <> 'Proforma' 
                and voyage_type in ('TC Relet','Voyage') order by invoice_date";
        }

        public static string GetAllPendingCommissionInvoice()
        {
            return @"select distinct(invoice_number), invoice_date, convert(char, pickup_date, 101) pickup_date, remarks from i_commission_invoice 
                where invoice_type in ('Commission Credit','Commission Debit') and pickup_flag = 0 and invoice_number <> 'Proforma' 
                order by invoice_date";
        }

        public static string GetAllPendingProforma()
        {
            return @"select 
                        distinct(invoice_number), 
                        company,
                        vessel_name,
                        voyage_no,
                        invoice_date, 
                        invoice_amount,
                        invoice_type,
                        convert(char, pickup_date, 101) pickup_date, 
                        remarks, 
                        profit_centre
                        from i_commission_invoice 
            where pickup_flag = 0 and invoice_number = 'Proforma' order by invoice_date";
        }

        public static string GetAllPendingTCReletCommissionCount()
        {
            return @"select count(*) from i_commission_invoice where invoice_type in ('Commission Credit','Commission Debit') and pickup_flag = 0 and invoice_number <> 'Proforma'";
        }


        public static string GetAllPendingFreightHireCommission()
        {
            return @"                    
select distinct(invoice_number), invoice_date, convert(char, pickup_date, 101) pickup_date, remarks from i_commission_invoice 
where invoice_type in ('Charge Debit','Commission Credit') 
and pickup_flag = 0 
and invoice_number <> 'Proforma' 
and voyage_type = 'Cargo Relet' 
--and voyage_type <> 'Cargo Relet' and invoice_type <> 'Charge Credit'
order by invoice_date";//'Charge Credit', statement
        }

        public static string GetAllPendingFreightHireInvoice()
        {
            return @"                    
select distinct(invoice_number), invoice_date, convert(char, pickup_date, 101) pickup_date, remarks from i_commission_invoice 
where invoice_type in ('Charge Debit') 
and pickup_flag = 0 
and invoice_number <> 'Proforma' 
order by invoice_date";//'Charge Credit', statement
        }

        public static string GetAllPendingFreightHireOUTCommission()
        {
            return @"                    
select distinct(invoice_number), invoice_date, convert(char, pickup_date, 101) pickup_date, remarks from i_commission_invoice 
where invoice_type in ('Charge Credit') and pickup_flag = 0 and invoice_number <> 'Proforma' 
and voyage_type = 'Cargo Relet'
order by invoice_date";
        }
        public static string GetAllPendingFreightHireCommissionCount()
        {
            return @"select count(*) from i_commission_invoice where invoice_type in ('Charge Credit','Charge Debit') and pickup_flag = 0 and invoice_number <> 'Proforma'";
        }
        public static string GetAllPendingTCReletCommission(string search)
        {
            return string.Format(@"select distinct(invoice_number), invoice_date, convert(char, pickup_date, 101) pickup_date, remarks from i_commission_invoice 
where invoice_type in ('Commission Credit','Commission Debit', 'Charge Debit') and invoice_number <> 'Proforma' and pickup_flag = 0 and voyage_type in ('TC Relet','Voyage')
and invoice_number +  replace(convert(char(11), invoice_date, 106),' ','-')+ replace(convert(char(11), pickup_date, 106),' ','-')+ remarks like '%{0}%'
order by invoice_date", search);
        }
        public static string GetAllPendingCommissionInvoice(string search)
        {
            return string.Format(@"select distinct(invoice_number), invoice_date, convert(char, pickup_date, 101) pickup_date, remarks from i_commission_invoice 
where invoice_type in ('Commission Credit','Commission Debit') and invoice_number <> 'Proforma' and pickup_flag = 0 
and invoice_number +  replace(convert(char(11), invoice_date, 106),' ','-')+ replace(convert(char(11), pickup_date, 106),' ','-')+ remarks like '%{0}%'
order by invoice_date", search);
        }
        public static string GetAllPendingProforma(string search)
        {
            return string.Format(@"select 
distinct(invoice_number), 
                        company,
                        vessel_name,
                        voyage_no,
                        invoice_date, 
                        invoice_amount,
                        invoice_type,
                        convert(char, pickup_date, 101) pickup_date, 
                        remarks, 
                        profit_centre
from i_commission_invoice
where invoice_number = 'Proforma' and pickup_flag = 0 
and profit_centre + invoice_number +  replace(convert(char(11), invoice_date, 106),' ','-')+ 
replace(convert(char(11), isnull(pickup_date, '01-01-1900'), 106),' ','-')+ isnull(remarks,'')+vessel_name+voyage_no+invoice_type  like '%{0}%'
order by invoice_date", search);
        }

        public static string GetAllPendingFreightHireCommission(string search)
        {
            return string.Format(@"select distinct(invoice_number), invoice_date, convert(char, pickup_date, 101) pickup_date, remarks from i_commission_invoice 
where invoice_type in ('Charge Debit', 'Commission Credit') 
and invoice_number <> 'Proforma' and pickup_flag = 0 
and voyage_type = 'Cargo Relet'
and invoice_number +  replace(convert(char(11), invoice_date, 106),' ','-')+ replace(convert(char(11), pickup_date, 106),' ','-')+ remarks like '%{0}%'
--and voyage_type <> 'Cargo Relet' and invoice_type <> 'Charge Credit'
order by invoice_date", search);//'Charge Credit', statement
        }

        public static string GetAllPendingFreightHireInvoice(string search)
        {
            return string.Format(@"select distinct(invoice_number), invoice_date, convert(char, pickup_date, 101) pickup_date, remarks from i_commission_invoice 
where invoice_type in ('Charge Debit') 
and invoice_number <> 'Proforma' and pickup_flag = 0 
and invoice_number +  replace(convert(char(11), invoice_date, 106),' ','-')+ replace(convert(char(11), pickup_date, 106),' ','-')+ remarks like '%{0}%'
--and voyage_type <> 'Cargo Relet' and invoice_type <> 'Charge Credit'
order by invoice_date", search);//'Charge Credit', statement
        }

        public static string GetAllPendingFreightHireOUTCommission(string search)
        {
            return string.Format(@"select distinct(invoice_number), invoice_date, convert(char, pickup_date, 101) pickup_date, remarks from i_commission_invoice 
where invoice_type in ('Charge Credit') and invoice_number <> 'Proforma' and pickup_flag = 0 
and invoice_number +  replace(convert(char(11), invoice_date, 106),' ','-')+ replace(convert(char(11), pickup_date, 106),' ','-')+ remarks like '%{0}%'
and voyage_type = 'Cargo Relet'
order by invoice_date", search);
        }

        public static string GetAllProforma()
        {
            return @"select distinct
                    invoice_date,
                    voyage_no,                    
                    vessel_name,
                    company,     
                    invoice_amount,
                    invoice_type
                    from i_commission_invoice  where invoice_number = 'Proforma'";
        }


        public static string GetAllProformaPending()
        {
            return @"select distinct
                    invoice_date,
                    voyage_no,                    
                    vessel_name,
                    company,     
                    invoice_amount,
                    invoice_type
                    from i_commission_invoice  where invoice_number = 'Proforma' and pickup_flag = 0";// and invoice_date > '2013-01-01 00:00:00'";
        }

        public static string GetAllProformaPendingDuringLastMonthPeriod()
        {
            return @"select distinct
                    invoice_date,
                    voyage_no,                    
                    vessel_name,
                    company,     
                    invoice_amount,
                    invoice_type
                    from i_commission_invoice  where invoice_number = 'Proforma' and pickup_flag = 0 
                        and invoice_date > @invoice_date_from and invoice_date < @invoice_date_to order by invoice_date";
        }

        public static string GetAllProformaChargeDebitPendingDuringLastMonthPeriod()
        {
            return @"select distinct
                    invoice_date,
                    voyage_no,                    
                    vessel_name,
                    company,     
                    invoice_amount,
                    invoice_type
                    from i_commission_invoice  where invoice_number = 'Proforma' and pickup_flag = 0 and invoice_type = 'Charge Debit' 
                        and invoice_date > @invoice_date_from and invoice_date < @invoice_date_to order by invoice_date";
        }

        public static string GetAllProformaCommissionCreditPendingDuringLastMonthPeriod()
        {
            return @"select distinct
                    invoice_date,
                    voyage_no,                    
                    vessel_name,
                    company,     
                    invoice_amount,
                    invoice_type
                    from i_commission_invoice  where invoice_number = 'Proforma' and pickup_flag = 0 and invoice_type = 'Commission Credit' 
                        and invoice_date > @invoice_date_from and invoice_date < @invoice_date_to order by invoice_date";
        }

        public static string GetAllProformaChargeCreditPendingDuringLastMonthPeriod()
        {
            return @"select distinct
                    invoice_date,
                    voyage_no,                    
                    vessel_name,
                    company,     
                    invoice_amount,
                    invoice_type
                    from i_commission_invoice  where invoice_number = 'Proforma' and pickup_flag = 0 and invoice_type = 'Charge Credit' 
                        and invoice_date > @invoice_date_from and invoice_date < @invoice_date_to order by invoice_date";
        }

        public static string GetProformaByInvoiceDateVoyageNoVesselCompanyAmount()
        {
            return @"select * from i_commission_invoice where invoice_number = 'Proforma' and invoice_date=@invoice_date and voyage_no=@voyage_no and vessel_name=@vessel_name and company=@company
            and invoice_amount=@invoice_amount";
        }

        public static string GetCollectCommission()
        {
            return @"select 
            VOPCHARGEFIXTURESRCH.CollectComm1 collect_comm,
            VOPCHARGEFIXTURESRCH.CollectComm2 collect_comm2,
            VOPCHARGEFIXTURESRCH.CollectComm3 collect_comm3,
            VOPCHARGEFIXTURESRCH.CollectComm4 collect_comm4
            from VOPCHARGEFIXTURESRCH 
            JOIN VVMOPFIXSRCH ON VVMOPFIXSRCH.OPSFIXTURE = VOPCHARGEFIXTURESRCH.FIXTURE 
            AND  VVMOPFIXSRCH.OPSOPTIONNO = VOPCHARGEFIXTURESRCH.OPSOPTIONNO 
            AND  VVMOPFIXSRCH.OPSVOYAGENO = VOPCHARGEFIXTURESRCH.OPSVOYAGENO 
            AND  VOPCHARGEFIXTURESRCH.Reversal = 0 
            and VOPCHARGEFIXTURESRCH.Reversed = 0 
            where VVMOPFIXSRCH.VMVOYAGENO = @vmvoyageno
            and VOPCHARGEFIXTURESRCH.Narrative = @narrative";
        }

        public static string GetAllCommission()
        {
            return @"select distinct
                    invoice_number
                    from i_commission_invoice  where invoice_number <> 'Proforma'";
        }

        public static string GetCommissionByInvoiceNumber()
        {
            return "select *  from i_commission_invoice  where invoice_number = @invoice_number";
        }

        public static string GetComissionInvoiceMiddleLayer()
        {
            return "select distinct invoice_number  from i_commission_invoice where pickup_flag in (0,1,2) and invoice_number <> 'Proforma'";
        }

        public static string GetAllChargeID()
        {
            return @"select commission_invoice_id, charge_id from i_commission_invoice where pickup_flag in (0,1) and charge_id > 0 and narrative is null";
        }

        public static string GetNarrationByChargeID()
        {
            return @"select VOPCHARGEFIXTURESRCH.Narrative from VOPCHARGEFIXTURESRCH where VOPCHARGEFIXTURESRCH.ID = @chargeid";
        }

        public static string UpdateNarrative()
        {
            return @"update i_commission_invoice set narrative = @narrative where commission_invoice_id=@commission_invoice_id";
        }

        public static string GetCommissionHistoryByInvoiceNumber(string invoice, string type)
        {
            return string.Format(@"select distinct batch_id,
            invoice_number,
            convert(datetime, convert(char(19), pickup_date, 126)) pickup_date
            from i_commission_invoice  where invoice_number like '%{0}%' and batch_id like '%{1}%' and pickup_flag = '1' ", invoice, type);
        }

        public static string GetCommissionHistoryByInvoiceNumberSC(string invoice, string type)
        {
            return string.Format(@"select distinct batch_id,
            statement_number invoice_number,
             convert(datetime, convert(char(19), pickup_date, 126)) pickup_date
            from i_statement  where statement_number like '%{0}%' and batch_id like '%SC%' and pickup_flag = '1' ", invoice, type);
        }

        public static string DeleteAllProforma()
        {
            return @"delete from i_commission_invoice where invoice_number = 'Proforma' AND pickup_flag in (0,2) ";
        }

        public static string GetCommisionInvoiceByInvoiceNo(string sql)
        {
            return @"
                 select 
	                    VVMVOYAGESRCH.ID,
	                    vInvoiceSrch.Type invoice_type,
	                    vInvoiceSrch.Company company,
	                    vVMVoyageSrch.VesselName vessel_name,
	                    vVMVoyageSrch.Vessel vessel_code,
	                    vVMVoyageSrch.VoyageNo voyage_no,
	                    vProfitCentreOrganisationSrch.ProfitCentreName profit_centre,
	                    vInvoiceSrch.DocInvDate invoice_date,----------
	                    VOPINVOICELINENARRATIVE.Narrative charge_description,
	                    vInvoiceSrch.InvoiceNo invoice_number,
	                    vInvoiceSrch.InvoiceTotal invoice_amount,
	                    vInvoiceSrch.Curr invoice_currency,
	                    vInvoiceSrch.DueDate invoice_due_date,
	                    VOPINVOICELINENARRATIVE.Type charge_type,
	                    vInvoiceSrch.RaisedToFinalBy raised_to_final_by,
	                    vInvoiceSrch.Paid paid,
	                    vInvoiceSrch.PaidDate paid_date,
	                    0 charge_addr_comm,
	                    VOPINVOICELINENARRATIVE.Charge charge_amount, 
	                    0 charge_collect_comm,
                        0 charge_non_collect_comm,
	                    vVMVoyageSrch.Type voyage_type,
	                    VOPCHARGEFIXTURESRCH.CollectComm1 collect_comm,
	                    VOPCHARGEFIXTURESRCH.CollectComm2 collect_comm2,
	                    VOPCHARGEFIXTURESRCH.CollectComm3 collect_comm3,
	                    VOPCHARGEFIXTURESRCH.CollectComm4 collect_comm4,
	                    VOPINVOICELINENARRATIVE.DisplayOrder display_order,
	                    VOPINVOICELINENARRATIVE.ChargeID charge_id,
	                    VOPINVOICELINENARRATIVE.NarrativeLine narrative_line,
	                    VOPCHARGEFIXTURESRCH.Narrative narrative		   
                                        from 
                     vInvoiceSrch 
                      join VVMVOYAGESRCH on VVMVOYAGESRCH.ID = VINVOICESRCH.VOYAGE 
                     join VOPINVOICELINENARRATIVE on
                     VOPINVOICELINENARRATIVE.InvoiceNo = VINVOICESRCH.No
                     and  VOPINVOICELINENARRATIVE.Fixture = VINVOICESRCH.Fix and
                                                    VOPINVOICELINENARRATIVE.InvoiceNo = vInvoiceSrch.No and
                                                    VOPINVOICELINENARRATIVE.Charge is not null  and
                                                    VOPINVOICELINENARRATIVE.VoyageNo =  vInvoiceSrch.VyNo and
                                                    VOPINVOICELINENARRATIVE.GroupNo = vInvoiceSrch.GrNo and
                                                    VOPINVOICELINENARRATIVE.OptionNo = vInvoiceSrch.OpNo
                     JOIN VPROFITCENTREORGANISATIONSRCH ON VVMVOYAGESRCH.ID = VPROFITCENTREORGANISATIONSRCH.ID       
                     join VOPCHARGEFIXTURESRCH on VOPCHARGEFIXTURESRCH.ID = VOPINVOICELINENARRATIVE.ChargeID
                    where
                    VOPINVOICELINENARRATIVE.Charge is not null
                    AND vVMVoyageSrch.Type in ('Cargo Relet','Voyage','TC Relet' )
                    AND vInvoiceSrch.InvoiceNo not like 'PPL%'
                    AND  vVMVoyageSrch.Category NOT LIKE 'UnFixed'  
                    AND  vInvoiceSrch.InvoiceNo = @InvoiceNo
                    AND  vProfitCentreOrganisationSrch.ProfitCentreName <>  'PACCSHIP (UK) LTD'
                    AND  vProfitCentreOrganisationSrch.ProfitCentreName <> 'PACNAV' "+sql+@"
                     order by 
                    vInvoiceSrch.InvoiceNo,
                    VOPINVOICELINENARRATIVE.DisplayOrder,
                    VOPINVOICELINENARRATIVE.ChargeID,
                    VOPINVOICELINENARRATIVE.NarrativeLine  
                    ";
        }
    }
}
